package com.ph.irp.base.measure.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.measure.dao.PBaseMeasureUnitDAO;
import com.ph.irp.model.PBaseMeasureunit;
import com.ph.irp.model.PBaseSettlement;
import com.ph.util.DBUtil;

/**
 * 计量单位
 * 
 * @author weide
 * @version 1.0 <br>
 *          Copyright (C), 2009, SongYandong <br>
 *          This program is protected by copyright laws. <br>
 *          Program Name: PBaseMeasureUnitDAOImpl.java<br>
 *          Date:2009-11-2
 */
public class PBaseMeasureUnitDAOImpl implements PBaseMeasureUnitDAO {

	/** The data source. */
	private ComboPooledDataSource dataSource;

	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * 添加
	 * 
	 * @param model
	 * @return null;
	 * 
	 */
	public void addMeasureUnit(PBaseMeasureunit model) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("insert into P_base_measureunit (measureUnitCode,measureUnitName,rates,type,Isdefault,measureGroupId) values (?,?,?,?,?,?)");
			pStmt.setString(1, model.getMeasureUnitCode());
			pStmt.setString(2, model.getMeasureUnitName());
			pStmt.setInt(3, model.getRates());
			pStmt.setString(4, model.getType());
			pStmt.setInt(5, model.getIsdefault());
			pStmt.setInt(6, model.getMeasureGroupId());

			pStmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}

	}

	/**
	 * 删除
	 * 
	 * @param id
	 * @return null;
	 * @throws SQLException
	 */
	public void deleteMeasureUnit(int id) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("delete from P_base_measureunit where measureUnitId = ?");
			pStmt.setInt(1, id);

			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}

	}

	/**
	 * 查询所有
	 * 
	 * @param model
	 * @return list
	 */
	@Override
	public List<PBaseMeasureunit> getAllMeasureUnit() throws Throwable {

		List<PBaseMeasureunit> measureList = new ArrayList<PBaseMeasureunit>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from P_base_measureunit");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(measureList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}

		return measureList;

	}

	/**
	 * 查询byID
	 * 
	 * @param id
	 * @return object
	 */
	@Override
	public PBaseMeasureunit getMeasureUnitById(int id) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseMeasureunit mu = new PBaseMeasureunit();

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from P_base_measureunit where measureUnitId = ?");
			pStmt.setInt(1, id);

			rs = pStmt.executeQuery();
			while (rs.next()) {

				mu.setMeasureUnitId(rs.getInt("measureUnitId"));
				mu.setMeasureGroupId(rs.getInt("measureGroupId"));
				mu.setMeasureUnitCode(rs.getString("measureUnitCode"));
				mu.setMeasureUnitName(rs.getString("measureUnitName"));
				mu.setRates(rs.getInt("rates"));
				mu.setType(rs.getString("type"));
				mu.setIsdefault(rs.getInt("Isdefault"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return mu;
	}

	public PBaseMeasureunit getDefaultMeasureUnitByGroupId(int id)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseMeasureunit mu = new PBaseMeasureunit();

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from P_base_measureunit where measureGroupId = ? and Isdefault=1");
			pStmt.setInt(1, id);

			rs = pStmt.executeQuery();
			while (rs.next()) {

				mu.setMeasureUnitId(rs.getInt("measureUnitId"));
				mu.setMeasureGroupId(rs.getInt("measureGroupId"));
				mu.setMeasureUnitCode(rs.getString("measureUnitCode"));
				mu.setMeasureUnitName(rs.getString("measureUnitName"));
				mu.setRates(rs.getInt("rates"));
				mu.setType(rs.getString("type"));
				mu.setIsdefault(rs.getInt("Isdefault"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return mu;

	}
	/**
	 * 根据唯一的name得到一条记录(For SellPrice)
	 * 
	 * @param model
	 * @return null;
	 */
	@Override
	public PBaseMeasureunit getMeasureUnitForSellPrice(String name)
			throws Throwable {

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseMeasureunit mu = new PBaseMeasureunit();

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from P_base_measureunit where measureUnitName like ?");
			pStmt.setString(1, "%" + name + "%");

			rs = pStmt.executeQuery();
			while (rs.next()) {

				mu.setMeasureUnitId(rs.getInt("measureUnitId"));
				mu.setMeasureGroupId(rs.getInt("measureGroupId"));
				mu.setMeasureUnitCode(rs.getString("measureUnitCode"));
				mu.setMeasureUnitName(rs.getString("measureUnitName"));
				mu.setRates(rs.getInt("rates"));
				mu.setType(rs.getString("type"));
				mu.setIsdefault(rs.getInt("Isdefault"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return mu;
	}
	/**
	 * 修改
	 * 
	 * @param model
	 * @return null;
	 */
	@Override
	public void updateMeasureUnit(PBaseMeasureunit model) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("update P_base_measureunit set measureUnitCode = ?,");
			sb.append("measureUnitName = ?," + "rates = ?," + "type = ?,");
			sb.append("Isdefault = ?," + "measureGroupId = ? ");
			sb.append("where measureUnitId = ?");

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.setString(1, model.getMeasureUnitCode());
			pStmt.setString(2, model.getMeasureUnitName());
			pStmt.setInt(3, model.getRates());
			pStmt.setString(4, model.getType());
			pStmt.setInt(5, model.getIsdefault());
			pStmt.setInt(6, model.getMeasureGroupId());
			pStmt.setInt(7, model.getMeasureUnitId());

			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	/**
	 * 查询by计量单位组ID
	 * 
	 * @param model
	 * @return null;
	 */
	@Override
	public List<PBaseMeasureunit> getMeasureUnitByGroupId(PBaseMeasureunit model)
			throws Throwable {
		List<PBaseMeasureunit> resList = new ArrayList<PBaseMeasureunit>();
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from P_base_measureunit where measureGroupId="
							+ model.getMeasureGroupId());

			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseMeasureunit mu = new PBaseMeasureunit();
				mu.setMeasureUnitId(rs.getInt("measureUnitId"));
				mu.setMeasureGroupId(rs.getInt("measureGroupId"));
				mu.setMeasureUnitCode(rs.getString("measureUnitCode"));
				mu.setMeasureUnitName(rs.getString("measureUnitName"));
				mu.setRates(rs.getInt("rates"));
				mu.setType(rs.getString("type"));
				mu.setIsdefault(rs.getInt("Isdefault"));

				resList.add(mu);
			}
		} catch (SQLException e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}

		return resList;
	}

	/**
	 * 动态查询
	 * 
	 * @param model
	 * @return null;
	 */

	public List<PBaseMeasureunit> dynamicQuery(PBaseMeasureunit model)
			throws Throwable {
		List<PBaseMeasureunit> resList = new ArrayList<PBaseMeasureunit>();
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select * from P_base_measureunit");
		sb.append(" where 1 = 1");
		if (model.getMeasureUnitId() != null) {
			sb.append(" and measureUnitId = ");
			sb.append(model.getMeasureUnitId());
		}
		if (Integer.valueOf(model.getMeasureGroupId()) != null) {
			sb.append(" and measureGroupId = ");
			sb.append(model.getMeasureGroupId());
		}
		if (Integer.valueOf(model.getIsdefault()) != null) {
			sb.append(" and Isdefault =");
			sb.append(model.getIsdefault());
		}

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseMeasureunit mu = new PBaseMeasureunit();
				mu.setMeasureUnitId(rs.getInt("measureUnitId"));
				mu.setMeasureGroupId(rs.getInt("measureGroupId"));
				mu.setMeasureUnitCode(rs.getString("measureUnitCode"));
				mu.setMeasureUnitName(rs.getString("measureUnitName"));
				mu.setRates(rs.getInt("rates"));
				mu.setType(rs.getString("type"));
				mu.setIsdefault(rs.getInt("Isdefault"));

				resList.add(mu);
			}
		} catch (Exception e) {
			// TODO: handle exception
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return resList;

	}

	/**
	 * 查询byCode
	 * 
	 * @param model
	 * @return null;
	 */
	@Override
	public List<PBaseMeasureunit> getMeasureUnitByCode(String code)
			throws Throwable {

		List<PBaseMeasureunit> measureList = new ArrayList<PBaseMeasureunit>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from P_base_measureunit where measureUnitCode like ?");
			// 给宿主变量绑定值
			statement.setString(1, "%" + code + "%");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(measureList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return measureList;

	}

	/**
	 * 查询byName
	 * 
	 * @param model
	 * @return null;
	 */
	@Override
	public List<PBaseMeasureunit> getMeasureUnitByName(String name)
			throws Throwable {

		List<PBaseMeasureunit> measureList = new ArrayList<PBaseMeasureunit>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from P_base_measureunit where measureUnitName like ?");
			// 给宿主变量绑定值
			statement.setString(1, "%" + name + "%");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(measureList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return measureList;
	}

	public PBaseMeasureunit getMeasureUnitByUnitName(String name)
			throws Throwable {
		PBaseMeasureunit pbaseMeasureunit = new PBaseMeasureunit();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from P_base_measureunit where measureUnitId =?");
			// 给宿主变量绑定值
			statement.setString(1, name);
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			while (resultSet.next()) {
				pbaseMeasureunit.setIsdefault(resultSet.getInt("isdefault"));
				pbaseMeasureunit.setMeasureGroupId(resultSet
						.getInt("measureGroupId"));
				pbaseMeasureunit.setMeasureUnitCode(resultSet
						.getString("measureUnitCode"));
				pbaseMeasureunit.setMeasureUnitId(resultSet
						.getInt("measureUnitId"));
				pbaseMeasureunit.setMeasureUnitName(resultSet
						.getString("measureUnitName"));
				pbaseMeasureunit.setRates(resultSet.getInt("rates"));
				pbaseMeasureunit.setType(resultSet.getString("type"));
			}

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return pbaseMeasureunit;

	}

	/**
	 * 查询byNameAndCode
	 * 
	 * @param model
	 * @return null;
	 */
	public List<PBaseMeasureunit> getMeasureUnitByNameAndCode(
			String measureUnitName, String measureUnitCode) throws Throwable {

		List<PBaseMeasureunit> measureList = new ArrayList<PBaseMeasureunit>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from P_base_measureunit where measureUnitName like ? and measureUnitCode like ?");
			// 给宿主变量绑定值
			statement.setString(1, "%" + measureUnitName + "%");
			statement.setString(2, "%" + measureUnitCode + "%");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(measureList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return measureList;
	}

	/**
	 * 迭代结果集，将数据封装到measureList中
	 * 
	 * @param settlementList
	 * @param resultSet
	 * @return
	 * @throws Throwable
	 */
	public List<PBaseMeasureunit> turnResultSetToList(
			List<PBaseMeasureunit> measureList, ResultSet resultSet)
			throws Throwable {

		// 迭代结果集
		while (resultSet.next()) {

			PBaseMeasureunit pbaseMeasureunit = new PBaseMeasureunit();

			pbaseMeasureunit.setIsdefault(resultSet.getInt("isdefault"));
			pbaseMeasureunit.setMeasureGroupId(resultSet
					.getInt("measureGroupId"));
			pbaseMeasureunit.setMeasureUnitCode(resultSet
					.getString("measureUnitCode"));
			pbaseMeasureunit
					.setMeasureUnitId(resultSet.getInt("measureUnitId"));
			pbaseMeasureunit.setMeasureUnitName(resultSet
					.getString("measureUnitName"));
			pbaseMeasureunit.setRates(resultSet.getInt("rates"));
			pbaseMeasureunit.setType(resultSet.getString("type"));

			measureList.add(pbaseMeasureunit);
		}

		return measureList;
	}
}
